USE [LAFDC]
GO
/****** Object:  StoredProcedure [dbo].[refreshSECTIONS]    Script Date: 11/23/2009 22:59:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[refreshSECTIONS] 
AS
BEGIN

	if exists ( select 1 from sys.objects where name='section' and type = 'U' ) 
	begin
			ALTER TABLE dbo.SECTION
				DROP CONSTRAINT PK_SECTION
	end

	if exists ( select 1 from sys.objects where name='section' and type = 'U' ) 
	begin
			drop table section
	end

	CREATE TABLE [dbo].[SECTION](
		[id] [int] IDENTITY(1,1) NOT NULL,
		[section id] [nvarchar](103) NOT NULL,
		[ad_id] [nvarchar](50) NULL,
		[SECTION_NAME] [nvarchar](50) NULL,
	 CONSTRAINT [PK_SECTION] PRIMARY KEY CLUSTERED 
	(
		[id] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	insert into section ([section id], [ad_id], [section_name])
	select *
	  from (
				select top 1000 'S-' + ad + '-' + section as 'section id', cast (ad as int) as 'ad_id', section as 'section_name'
				  from adherent
				 group by AD, section
				 order by ad, section
			) temp
--	 order by [ad_id], [section_name]
END
